//
//  FriendRespondDateBase.m
//  SIPphone
//
//  Created by wrt on 13-11-28.
//  Copyright (c) 2013年 wrtsoft. All rights reserved.
//

#import "FriendRespondDateBase.h"

//数据库对象的单例
static FriendRespondDateBase *shareFriendRespondDateBase = nil;

@implementation FriendRespondDateBase
{
    //从数据库中读出来的暂存的字符串
    NSString *tempUserName;
    NSString *tempDisplayName;
    NSString *tempIpPort;
    NSString *tempTime;
    NSString *tempIsRead;
    NSString *tempIsRqdOrRjt;
    NSString *tempIsDo;
}

//单例初始化方法
+ (FriendRespondDateBase *)shareFriendRespondDateBase
{
    @synchronized(self)
    {
        if (shareFriendRespondDateBase == nil)
        {
            shareFriendRespondDateBase = [[self alloc] init];
        }
    }
    return shareFriendRespondDateBase;
}

//切换表名
- (void)changeTableName:(NSString *)userName
{
    self.tableNameForUser = [[NSString alloc] init];
    self.tableNameForUser = [NSString stringWithFormat:@"friendRespondTable_%@",userName];
}

//创建数据库
- (void)creatDB
{
    //在一个新线程中进行数据库存储操作
    //查找一个可用的全局队列
//    dispatch_queue_t queue = dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0);
//    //将要做的操作放入队列
//    dispatch_async(queue,^{
    
        NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *myDocPath = myPaths[0];
        NSString *filename = [myDocPath stringByAppendingPathComponent:@"friendRespond.db"];
        //打开数据库
        if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
        {
            //关闭数据库
            sqlite3_close(db);
        }
        else
        {
            //创建表
            char *error;
            NSString *createSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(%@ TEXT,%@ TEXT,%@ TEXT,%@ TEXT,%@ TEXT,%@ TEXT,%@ TEXT)",self.tableNameForUser,FRIENDRPD_TIME,FRIENDRPD_USERNAME,FRIENDRPD_DISPLAYNAME,FRIENDRPD_IPPORT,FRIENDRPD_ISREAD,FRIENDRPD_ISRQTORJT,FRIENDRPD_ISDO];
            //运行SQL语句
            if(sqlite3_exec(db, [createSQL UTF8String], NULL, NULL, &error) != SQLITE_OK
               )
            {
                NSLog(@"建表失败！%s",error);
            }
            //关闭数据库
            sqlite3_close(db);
        }
//    });
}
//读取数据库
- (void)readDB
{
    dispatch_async(dispatch_get_main_queue(),^{
        //将所需要存数据的数组初始化
        self.userNameArray = [[NSMutableArray alloc] init];
        self.displayNameArray = [[NSMutableArray alloc] init];
        self.timeArray = [[NSMutableArray alloc] init];
        self.ipPortArray = [[NSMutableArray alloc] init];
        self.isReadArray = [[NSMutableArray alloc] init];
        self.isRqtOrRjtArray = [[NSMutableArray alloc] init];
        self.isDoArray = [[NSMutableArray alloc] init];
        
        //数据库先执行一遍读
        NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *myDocPath = myPaths[0];
        NSString *filename = [myDocPath stringByAppendingPathComponent:@"friendRespond.db"];
        
        //打开数据库
        if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
        {
            //关闭数据库
            sqlite3_close(db);
        }
        else
        {
            NSString *sqlStr = [NSString stringWithFormat:@"SELECT %@,%@,%@,%@,%@,%@,%@ FROM %@ ORDER BY %@ ",FRIENDRPD_TIME,FRIENDRPD_USERNAME,FRIENDRPD_DISPLAYNAME,FRIENDRPD_IPPORT,FRIENDRPD_ISREAD,FRIENDRPD_ISRQTORJT,FRIENDRPD_ISDO,self.tableNameForUser,FRIENDRPD_TIME];
            
            sqlite3_stmt *statement;
            
            //预处理
            if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statement, NULL) == SQLITE_OK)
            {
                //执行
                while(sqlite3_step(statement) == SQLITE_ROW)
                {
                    char *text1 = (char *)sqlite3_column_text(statement, 0);
                    tempTime = [[NSString alloc] initWithUTF8String:text1];
                    
                    char *text2 = (char *)sqlite3_column_text(statement, 1);
                    tempUserName = [[NSString alloc] initWithUTF8String:text2];
                    
                    char *text3 = (char *)sqlite3_column_text(statement, 2);
                    if (text3 == nil || !(strlen(text3) > 0))
                    {
                        tempDisplayName = tempUserName;
                    }
                    else
                    {
                        tempDisplayName = [[NSString alloc] initWithUTF8String:text3];
                    }
                    
                    char *text4 = (char *)sqlite3_column_text(statement, 3);
                    tempIpPort = [[NSString alloc] initWithUTF8String:text4];
                    
                    char *text5 = (char *)sqlite3_column_text(statement, 4);
                    tempIsRead = [[NSString alloc] initWithUTF8String:text5];
                    
                    char *text6 = (char *)sqlite3_column_text(statement, 5);
                    tempIsRqdOrRjt = [[NSString alloc] initWithUTF8String:text6];
                    
                    char *text7 = (char *)sqlite3_column_text(statement, 6);
                    tempIsDo = [[NSString alloc] initWithUTF8String:text7];
                    
                    
                    if (tempDisplayName.length > 0 && tempIpPort.length > 0 && tempIsDo.length > 0 && tempIsRead.length > 0 && tempIsRqdOrRjt.length > 0 && tempTime.length > 0 && tempUserName.length > 0)
                    {
                        [self.timeArray addObject:tempTime];
                        [self.userNameArray addObject:tempUserName];
                        [self.displayNameArray addObject:tempDisplayName];
                        [self.ipPortArray addObject:tempIpPort];
                        [self.isReadArray addObject:tempIsRead];
                        [self.isRqtOrRjtArray addObject:tempIsRqdOrRjt];
                        [self.isDoArray addObject:tempIsDo];
                    }
                }
                //释放状态资源
                sqlite3_finalize(statement);
                //关闭数据库
                sqlite3_close(db);
            }
            
        }
    });
}

//保存数据库
- (void)saveFriendRequstDBWithUserName:(NSString *)userName andDisPlayName:(NSString *)disPlayname andIPport:(NSString *)ip andTime:(NSString *)time andType:(NSString *)type
{
    //在一个新线程中进行数据库存储操作
    //查找一个可用的全局队列
//    dispatch_queue_t queue = dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0);
//    //将要做的操作放入队列
//    dispatch_async(queue,^{
        //数据库写操作
        NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *myDocPath = myPaths[0];
        NSString *filename = [myDocPath stringByAppendingPathComponent:@"friendRespond.db"];
        //打开数据库
        if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
        {
            //关闭数据库
            sqlite3_close(db);
        }
        else
        {
            //INSERT OR REPLACE INTO tablename(text,text,...,integer) VALUES(?,?,?,?,?,?)
            NSString *sqlStr = [NSString stringWithFormat:@"INSERT OR REPLACE INTO %@(%@,%@,%@,%@,%@,%@,%@) VALUES(?,?,?,?,?,?,?)",self.tableNameForUser,FRIENDRPD_TIME,FRIENDRPD_USERNAME,FRIENDRPD_DISPLAYNAME,FRIENDRPD_IPPORT,FRIENDRPD_ISREAD,FRIENDRPD_ISRQTORJT,FRIENDRPD_ISDO];
            sqlite3_stmt *statment;
            //预处理
            if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statment, NULL) == SQLITE_OK)
            {
                //绑定参数
                sqlite3_bind_text(statment, 1, [time UTF8String], -1, NULL);
                sqlite3_bind_text(statment, 2, [userName UTF8String], -1, NULL);
                sqlite3_bind_text(statment, 3, [disPlayname UTF8String], -1, NULL);
                sqlite3_bind_text(statment, 4, [ip UTF8String], -1, NULL);
                sqlite3_bind_text(statment, 5, [@"未读" UTF8String], -1, NULL);
                sqlite3_bind_text(statment, 6, [type UTF8String], -1, NULL);
                sqlite3_bind_text(statment, 7, [@"未处理" UTF8String], -1, NULL);
                //执行插入
                if(sqlite3_step(statment) != SQLITE_DONE)
                {
                    NSLog(@"插入失败");
                }
            }
            
            //释放状态资源
            sqlite3_finalize(statment);
            //关闭数据库
            sqlite3_close(db);
        }
        [self readDB];
//    });
}
//数据库删除
- (void)deleteFriendBDWithUserName:(NSString *)userName andTime:(NSString *)time
{
    
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"friendRespond.db"];
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        NSString *sqlStr = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = '%@' AND %@ = '%@'",self.tableNameForUser,FRIENDRPD_USERNAME,userName,FRIENDRPD_TIME,time];
        char *errorMsg;
        if (sqlite3_exec(db, [sqlStr UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK)
        {
            NSLog(@"OK");
        }
        //关闭数据库
        sqlite3_close(db);
    }
}

//数据库全部删除
- (void)removeAllFriendRespond
{
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"friendRespond.db"];
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        NSString *sqlStr = [NSString stringWithFormat:@"DELETE FROM %@ WHERE 1 > 0",self.tableNameForUser];
        char *errorMsg;
        if (sqlite3_exec(db, [sqlStr UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK)
        {
            NSLog(@"OK");
        }
        //关闭数据库
        sqlite3_close(db);
    }
    [self.userNameArray removeAllObjects];
    [self.displayNameArray removeAllObjects];
    [self.timeArray removeAllObjects];
    [self.ipPortArray removeAllObjects];
    [self.isReadArray removeAllObjects];
    [self.isRqtOrRjtArray removeAllObjects];
    [self.isDoArray removeAllObjects];
}

//数据库更新(判断是否已读)
- (void)updateDBForReadWithUserName:(NSString *)userName andTime:(NSString *)time
{
    //在一个新线程中进行数据库存储操作
    //查找一个可用的全局队列
//    dispatch_queue_t queue = dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0);
//    //将要做的操作放入队列
//    dispatch_async(queue,^{
    
        NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *myDocPath = myPaths[0];
        NSString *filename = [myDocPath stringByAppendingPathComponent:@"friendRespond.db"];
        
        //打开数据库
        if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
        {
            //关闭数据库
            sqlite3_close(db);
        }
        else
        {
            //INSERT OR REPLACE INTO tablename(text,text,...,integer) VALUES(?,?,?,?,?,?)
            NSString *sqlStr = [NSString stringWithFormat:@"UPDATE %@ SET %@ = '%@' WHERE %@ = '%@' AND %@ = '%@'",self.tableNameForUser,FRIENDRPD_ISREAD,@"已读",FRIENDRPD_USERNAME,userName,FRIENDRPD_TIME,time];
            sqlite3_stmt *statment;
            //预处理
            if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statment, NULL) == SQLITE_OK)
            {
                //执行更新
                if(sqlite3_step(statment) != SQLITE_DONE)
                {
                    NSLog(@"更新失败");
                }
            }
            //释放状态资源
            sqlite3_finalize(statment);
            //关闭数据库
            sqlite3_close(db);
        }
        [self readDB];
//    });
}
//数据库更新(判断是否进行了操作)
- (void)updateDBForIsDoWithUserName:(NSString *)userName andIP:(NSString *)ip
{
//    //在一个新线程中进行数据库存储操作
//    //查找一个可用的全局队列
//    dispatch_queue_t queue = dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0);
//    //将要做的操作放入队列
//    dispatch_async(queue,^{
    
        NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *myDocPath = myPaths[0];
        NSString *filename = [myDocPath stringByAppendingPathComponent:@"friendRespond.db"];
        
        //打开数据库
        if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
        {
            //关闭数据库
            sqlite3_close(db);
        }
        else
        {
            //INSERT OR REPLACE INTO tablename(text,text,...,integer) VALUES(?,?,?,?,?,?)
            NSString *sqlStr = [NSString stringWithFormat:@"UPDATE %@ SET %@ = '%@' WHERE %@ = '%@' AND %@ = '%@'",self.tableNameForUser,FRIENDRPD_ISDO,@"已处理",FRIENDRPD_USERNAME,userName,FRIENDRPD_IPPORT,ip];
            sqlite3_stmt *statment;
            //预处理
            if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statment, NULL) == SQLITE_OK)
            {
                //执行更新
                if(sqlite3_step(statment) != SQLITE_DONE)
                {
                    NSLog(@"更新失败");
                }
            }
            //释放状态资源
            sqlite3_finalize(statment);
            //关闭数据库
            sqlite3_close(db);
        }
        [self readDB];
//    });
}

//数据库更新(更新用户昵称)
- (void)updateDBForDisplayNameWithUserName:(NSString *)userName andIP:(NSString *)ip andDisplayName:(NSString *)displayname
{
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"friendRespond.db"];
    
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //INSERT OR REPLACE INTO tablename(text,text,...,integer) VALUES(?,?,?,?,?,?)
        NSString *sqlStr = [NSString stringWithFormat:@"UPDATE %@ SET %@ = '%@' WHERE %@ = '%@' AND %@ = '%@'",self.tableNameForUser,FRIENDRPD_DISPLAYNAME,displayname,FRIENDRPD_USERNAME,userName,FRIENDRPD_IPPORT,ip];
        sqlite3_stmt *statment;
        //预处理
        if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statment, NULL) == SQLITE_OK)
        {
            //执行更新
            if(sqlite3_step(statment) != SQLITE_DONE)
            {
                NSLog(@"更新失败");
            }
        }
        //释放状态资源
        sqlite3_finalize(statment);
        //关闭数据库
        sqlite3_close(db);
    }
}
//获取未读的条数
- (int)countForUnreadWith
{
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"friendRespond.db"];
    
    int i = 0;
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //INSERT OR REPLACE INTO tablename(text,text,...,integer) VALUES(?,?,?,?,?,?)
        NSString *sqlStr = [NSString stringWithFormat:@"SELECT COUNT(*) FROM %@ WHERE %@ = '%@'",self.tableNameForUser,FRIENDRPD_ISREAD,@"未读"];
        sqlite3_stmt *statment;
        //预处理
        if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statment, NULL) == SQLITE_OK)
        {
            while (sqlite3_step(statment) == SQLITE_ROW)
            {
                i = sqlite3_column_int(statment, 0);
            }
        }
        //释放状态资源
        sqlite3_finalize(statment);
        //关闭数据库
        sqlite3_close(db);
    }
    return i;
}

//获取部分信息15一组
- (void)readDBForpartWithBeginNumber:(int)begin
{
    self.partUserNameArray = [[NSMutableArray alloc] init];
    self.partDisplayNameArray = [[NSMutableArray alloc] init];
    self.partIpPortArray = [[NSMutableArray alloc] init];
    self.partIsReadArray = [[NSMutableArray alloc] init];
    self.partTimeArray = [[NSMutableArray alloc] init];
    self.partIsRqtOrRjtArray = [[NSMutableArray alloc] init];
    self.partIsDoArray = [[NSMutableArray alloc] init];
    
    //数据库先执行一遍读
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"friendRespond.db"];
    
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        NSString *sqlStr = [NSString stringWithFormat:@"SELECT %@,%@,%@,%@,%@,%@,%@ FROM %@ ORDER BY %@ desc limit %d,15",FRIENDRPD_TIME,FRIENDRPD_USERNAME,FRIENDRPD_DISPLAYNAME,FRIENDRPD_IPPORT,FRIENDRPD_ISREAD,FRIENDRPD_ISRQTORJT,FRIENDRPD_ISDO,self.tableNameForUser,FRIENDRPD_TIME,begin];
        
        sqlite3_stmt *statement;
        
        //预处理
        if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statement, NULL) == SQLITE_OK)
        {
            //执行
            while(sqlite3_step(statement) == SQLITE_ROW)
            {
                char *text1 = (char *)sqlite3_column_text(statement, 0);
                tempTime = [[NSString alloc] initWithUTF8String:text1];
                [self.partTimeArray addObject:tempTime];
    
                char *text2 = (char *)sqlite3_column_text(statement, 1);
                tempUserName = [[NSString alloc] initWithUTF8String:text2];
                [self.partUserNameArray addObject:tempUserName];
                
                char *text3 = (char *)sqlite3_column_text(statement, 2);
                if (text3 == nil || !(strlen(text3) > 0))
                {
                    tempDisplayName = tempUserName;
                }
                else
                {
                    tempDisplayName = [[NSString alloc] initWithUTF8String:text3];
                }
                [self.partDisplayNameArray addObject:tempDisplayName];
                
                char *text4 = (char *)sqlite3_column_text(statement, 3);
                tempIpPort = [[NSString alloc] initWithUTF8String:text4];
                [self.partIpPortArray addObject:tempIpPort];
                
                char *text5 = (char *)sqlite3_column_text(statement, 4);
                tempIsRead = [[NSString alloc] initWithUTF8String:text5];
                [self.partIsReadArray addObject:tempIsRead];
                
                char *text6 = (char *)sqlite3_column_text(statement, 5);
                tempIsRqdOrRjt = [[NSString alloc] initWithUTF8String:text6];
                [self.partIsRqtOrRjtArray addObject:tempIsRqdOrRjt];
                
                char *text7 = (char *)sqlite3_column_text(statement, 6);
                tempIsDo = [[NSString alloc] initWithUTF8String:text7];
                [self.partIsDoArray addObject:tempIsDo];
            }
            //释放状态资源
            sqlite3_finalize(statement);
            //关闭数据库
            sqlite3_close(db);
        }
    }
}
@end
